package com.yifi.common.base;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.log4j.Logger;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.DbPro;
import com.jfinal.plugin.activerecord.Model;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.activerecord.SqlPara;
import com.jfinal.plugin.activerecord.Table;
import com.jfinal.plugin.activerecord.TableMapping;
import com.yifi.common.utils.StringUtils;
import com.yifi.common.utils.text.Convert;
import com.yifi.common.vo.Grid;

public abstract class Base1Service {
	
	protected Logger logger = Logger.getLogger(getClass());
	
	/**
	 * 获取model dao
	 * @return 业务Model
	 */
	public abstract Model<?> getDao();

	/**
	 * 指定数据源,多数据源情况下使用<br/>
	 * 列如oracle数据源别名为oracle，在service重写该方法:
	 * <pre>
	 * @Override
	 * public String getDb(){
	 *	return "oracle";
	 * }
	 * </pre>
	 */
	public String getDb(){
		return null;
	}
	
	/**
	 * 获取DBPro数据源
	 * @return
	 */
	private DbPro getDbPro(){
		if(getDb()!=null){
			return Db.use(getDb());
		}
		return Db.use();
	}
	
	/**
	 * 获取table名称
	 * @return tableName
	 */
	public String getTable() {
		return _getTable().getName();
	};
	
	/**
	  *  获取表主键（单键表）
	 * @return
	 */
	public String getPK() {
		return _getTable().getPrimaryKey()[0];
	}
	
	protected Table _getTable() {
		if(getDao()==null){
			logger.error("请实现getDao()方法,且不能返回null");
		}
		return TableMapping.me().getTable(getDao().getClass());
	}
	
	/**
	 * 通用findById
	 * @param id
	 * @return
	 */
	public Model<?> selectById(String id){
		return getDao().findById(id);
	}

	public Model<?> selectById(Long id){
		return getDao().findById(id);
	}
	public Model<?> selectById(Integer id){
		return getDao().findById(id);
	}
	/**
	 * 通过字段查找对象数据
	 * @param pk 字段名
	 * @param value 字段值
	 * @return
	 */
	public Model<?> selectByIdPk(String pk,String value){
		List<?> list=getDao().find(getQuerySql()+" where "+pk+"=?", value);
		if(list.size()>0){
			return (Model<?>) list.get(0);
		}
		return null;
	}
	
	/**
	 * 通用save
	 * @param entity
	 * @return
	 */
	public boolean save(Model<?> entity){
		String pk=getPK();
		String pkStr="";
		if(entity.get(pk)!=null)
		{
			pkStr=entity.get(pk).toString();
		}
		Model<?> entity2=selectById(pkStr);
		if (StringUtils.isNotNull(entity2)) {
			return update(entity);
		} else {
			return insert(entity);
		}
	}

	public boolean insert(Model<?> entity){
		//主键赋值uuid
		//entity.set(getPK(), UUID.randomUUID().toString());
		return entity.save();
	}
	/**
	 * 通用update
	 * @param entity
	 * @return
	 */
	public boolean update(Model<?> entity){
		return entity.update();
	}
	
	/**
	 * 通用delete
	 * @param entity
	 * @return
	 */
	public boolean delete(Model<?> entity){
		return entity.delete();
	}
	
	/**
	 * 通用deleteById
	 * @param id
	 * @return
	 */
	public boolean deleteById(String id){
		return getDao().deleteById(id);
	}
	
	/**
	 * 通用deleteByIds
	 * @param ids
	 */
	public void deleteByIds(List<String> ids){
		Object[][] paras=new Object[ids.size()][1];
		for(int i=0;i<ids.size();i++) {
			paras[i][0]=ids.get(i);
		}
		String sql="delete from "+getTable()+" where id=?";
		getDbPro().batch(sql, paras, 100);
	}
	/**
	 * 通用deleteByIds
	 * @param ids
	 */
	public int deleteByIds(String ids){
		String adis = Convert.toDelIds(ids);
		String sql = "delete from "+getTable()+" where "+getPK()+" in (" + adis + ")";
		return Db.update(sql);
	}
	/**
	 * 根据字段删除数据
	 * @param ids
	 * @param pk
	 */
	public void deleteByPk(List<String> ids,String pk){
		Object[][] paras=new Object[ids.size()][1];
		for(int i=0;i<ids.size();i++) {
			paras[i][0]=ids.get(i);
		}
		String sql="delete from "+getTable()+" where "+pk+"=?";	
		getDbPro().batch(sql, paras, 100);			
	}
	
	/**
	 * 是否存在对象数据
	 * @param pk
	 * @param value
	 * @return
	 */
	public boolean isExit(String pk,String value){
		List<?> list=getDbPro().find(getQuerySql()+"where "+pk+"=?", value);
		return list.size()>0;
	}

	public List<?> selectList()
	{
		return getDao().find(getQuerySql());
	}
	public List<?> selectList(Model<?> entity)
	{
		List<Object> paras = new ArrayList<>();
		String sql = this.createQuerySqlModel(getQuerySql(),entity,paras);
		return getDao().find(sql, paras.toArray());
	}
	public List<?> selectList(Model<?> entity,String queryType)
	{
		List<Object> paras = new ArrayList<>();
		String sql = this.createQuerySqlModel(getQuerySql(),null,entity,paras,queryType);
		return getDao().find(sql, paras.toArray());
	}

	public Page<?> selectPageList(Model<?> entity,String orderByGroupBy,int pageNum,int pageSize)
	{
		List<Object> paras = new ArrayList<>();
		String sqlSelect=getSelectSql();
		String sqlExcept = this.getExceptSql(orderByGroupBy,entity,paras,"like");
		return getDao().paginate(pageNum,pageSize,sqlSelect,sqlExcept,paras.toArray());
	}

	public Page<?> selectPageList(Model<?> entity,HashMap<String, String> map,String orderByGroupBy,int pageNum,int pageSize)
	{
		List<Object> paras = new ArrayList<>();
		String sqlSelect=getSelectSql();
		String sqlExcept = this.getExceptSql("",orderByGroupBy,entity,map,paras,"like");
		return getDao().paginate(pageNum,pageSize,sqlSelect,sqlExcept,paras.toArray());
	}
	
	
	public List<Record> queryAllList() {
		return getDbPro().find(getQuerySql());
	}
	
	public List<Record> queryAllList(String groupOrderBy) {
		return getDbPro().find(getQuerySql()+groupOrderBy);
	}
	
	public List<Record> queryForList(String sql) {
		return getDbPro().find(sql);
	}
	
	public List<Record> queryForList(String sql,Object...object) {
		return getDbPro().find(sql,object);
	}
	public List<Record> queryForList(String sql,Record record) {
		return queryForList(sql,record,null);
	}
	
	public List<Record> queryForList(String sql,Record record,String groupOrderBy){
		List<Object> paras = new ArrayList<>();
		sql = this.createQuerySql(sql, groupOrderBy, record, paras, "like");
		return getDbPro().find(sql, paras.toArray());	
	}
	
	public List<Record> queryForListEq(String sql,Record record,String groupOrderBy){
		List<Object> paras = new ArrayList<>();
		sql = this.createQuerySql(sql, groupOrderBy, record, paras, "=");
		return getDbPro().find(sql, paras.toArray());
	}
	

	
	/**
	 * 自定义分页查询
	 * @param sql
	 * @param pageNumber
	 * @param pageSize
	 * @param record
	 * @param groupOrderBy
	 * @return
	 */
	public Grid queryForList(String sql,int pageNumber,int pageSize,Record record,String groupOrderBy){
		List<Object> paras = new ArrayList<>();
		sql = this.createQuerySql(sql, groupOrderBy, record, paras, "like");
		return getGrid(pageNumber, pageSize, sql, paras.toArray());
	}
	
	public Grid queryForListEq(String sql,int pageNumber,int pageSize,Record record,String groupOrderBy){
		List<Object> paras = new ArrayList<>();
		sql = this.createQuerySql(sql, groupOrderBy, record, paras, "=");
		return getGrid(pageNumber, pageSize, sql, paras.toArray());
	}
	
	public Grid queryForList(int pageNumber,int pageSize,String sql){
		return getGrid(pageNumber, pageSize, sql);
	}
	
	public Grid queryForList(int pageNumber,int pageSize,String sql,Object... object){
		return getGrid(pageNumber, pageSize, sql, object);	
	}
	
	public Grid queryForList(int pageNumber,int pageSize){
		return getGrid( pageNumber, pageSize,getQuerySql());
	}
	
	public Grid queryForList(int pageNumber,int pageSize,Record record){
		List<Object> paras=new ArrayList<>();
		String sql=createQuerySql(getQuerySql(), null, record, paras, "like");
		return getGrid( pageNumber, pageSize,sql,paras.toArray());
	}
	
	public Grid queryForList(int pageNumber,int pageSize,Record record,String orderBygroupBySql){
		List<Object> paras=new ArrayList<>();
		String sql=createQuerySql(getQuerySql(), orderBygroupBySql, record, paras, "like");
		return getGrid( pageNumber, pageSize,sql,paras.toArray());
	}
	
	/**
	 * 全等查询
	 * @param pageNumber
	 * @param pageSize
	 * @param record
	 * @return
	 */
	public Grid queryForListEq(int pageNumber,int pageSize,Record record){
		List<Object> paras=new ArrayList<>();
		String sql=createQuerySql(getQuerySql(), null, record, paras, "=");
		return getGrid( pageNumber, pageSize,sql,paras.toArray());
	}
	
	/**
	 * 全等查询
	 * @param pageNumber
	 * @param pageSize
	 * @param record
	 * @param orderBygroupBySql
	 * @return
	 */
	public Grid queryForListEq(int pageNumber,int pageSize,Record record,String orderBygroupBySql){
		List<Object> paras=new ArrayList<>();
		String sql=createQuerySql(getQuerySql(), orderBygroupBySql, record, paras, "=");
		return getGrid( pageNumber, pageSize,sql,paras.toArray());
	}
				
	/**
	 * 分页,模糊查询
	 * @param grid
	 * @param record columns查询元素集合  
	 * @return
	 */
	public Grid queryForList(Grid grid, Record record) {
		List<Object> paras=new ArrayList<>();
		String sql = this.createQuerySql(getQuerySql(), null, record, paras, "like");
		return getGrid(grid.getPageNumber(),grid.getPageSize(),sql,paras.toArray());
	}
	
	/**
	 * 分页查询,分组排序
	 * @param grid
	 * @param orderBygroupBySql
	 * @return
	 */
	public Grid queryForList(Grid grid, String orderBygroupBySql) {
		List<Object> paras=new ArrayList<>();
		String sql = this.createQuerySql(getQuerySql(), orderBygroupBySql, null, paras, "like");
		return getGrid(grid.getPageNumber(),grid.getPageSize(),sql,paras.toArray());
	}

	/**
	 * 分页,模糊查询,分组排序
	 * @param grid
	 * @param record columns查询元素集合
	 * @param orderBygroupBySql 分组排序
	 * 
	 */
	public Grid queryForList(Grid grid, Record record, String orderBygroupBySql) {
		List<Object> paras = new ArrayList<>();
		String sql = this.createQuerySql(getQuerySql(), orderBygroupBySql, record, paras, "like");
		return getGrid(grid.getPageNumber(),grid.getPageSize(),sql,paras.toArray());
	}

	private Grid getGrid(int pageNumber,int pageSize,String sql,Object... paras){
		SqlPara sqlPara=new SqlPara().setSql(sql);
		for(int i=0;i<paras.length;i++){
			sqlPara.addPara(paras[i]);
		}
		
		Page<Record> page=getDbPro().paginate(pageNumber, pageSize, sqlPara);
		return new Grid(page.getList(), pageNumber, pageSize, page.getTotalRow());
	}
	
	private Grid getGrid(int pageNumber,int pageSize,String sql){
		SqlPara sqlPara=new SqlPara().setSql(sql);
		Page<Record> page=getDbPro().paginate(pageNumber, pageSize, sqlPara);
		return new Grid(page.getList(), pageNumber, pageSize, page.getTotalRow());
	}
	
	/**
	 * 拼接模糊查询条件
	 * 
	 * @param sql
	 * @param orderByGroupBySql
	 * @param record
	 *            columns查询元素集合
	 * @param paras
	 * @param queryType
	 *            like or = ，模糊查询或者全等查询
	 * @return
	 */
	private String createQuerySql(String sql, String orderByGroupBySql, Record record, List<Object> paras,
			String queryType) {
		if(record==null){
			return orderByGroupBySql == null ? sql : sql + " " + orderByGroupBySql;
		}
		
		Map<String,Object> columns=record.getColumns();
        Iterator<String> iter=columns.keySet().iterator();
        StringBuffer whereSql=new StringBuffer();
        
        while(iter.hasNext()){
        	String column=iter.next();
        	Object value=columns.get(column);
        	
        	if(value!=null&&value.toString().trim().length()>0){
        		if(whereSql.length()>0){
        			whereSql.append(" and ");
        		}
        		//用法看用户管理查询功能
        		if(column.endsWith("=")){                              //column=、column<=、column>=
        			whereSql.append(column).append(" ? ");
					paras.add(value);
        		} else if(column.endsWith(">")||column.endsWith("<")){ //column<、column>
        			whereSql.append(column).append(" ? ");
        			paras.add(value);
        		}else if(column.toLowerCase().endsWith("like")){       //column like
        			whereSql.append(column).append(" ? ");
					paras.add("%" + value + "%");
        		}else if("=".equals(queryType)) {
					whereSql.append(column).append(" =? ");
					paras.add(value);
				}else{
					whereSql.append(column).append(" like ? ");
					paras.add("%" + value + "%");
				}
        	}
        }

        if(whereSql.length()>0){
        	if(sql.toLowerCase().contains("where")){
        		sql+=" and "+whereSql.toString();
        	}else{
        		sql+=" where "+whereSql.toString();
        	}        	
        }
        
		if (orderByGroupBySql != null) {
			sql += " " + orderByGroupBySql;
		}
		
        return sql;
	}


	public String createQuerySqlModel(String sql,Model<?> entity,List<Object> paras) {
		return createQuerySqlModel(sql,null,entity,paras,"=");
	}

	private String createQuerySqlModel(String sql,String orderByGroupBySql,Model<?> entity,List<Object> paras,
								  String queryType) {
		if(entity==null){
			return orderByGroupBySql == null ? sql : sql + " " + orderByGroupBySql;
		}
		String[] columns=entity._getAttrNames();
		Object[] values=entity._getAttrValues();
		StringBuffer whereSql=new StringBuffer();
		for (String column:columns) {
			Object value=entity.get(column);
			if(value!=null&&value.toString().trim().length()>0){
				if(whereSql.length()>0){
					whereSql.append(" and ");
				}
				//用法看用户管理查询功能
				if(column.endsWith("=")){                              //column=、column<=、column>=
					whereSql.append(column).append(" ? ");
					paras.add(value);
				} else if(column.endsWith(">")||column.endsWith("<")){ //column<、column>
					whereSql.append(column).append(" ? ");
					paras.add(value);
				}else if(column.toLowerCase().endsWith("like")){       //column like
					whereSql.append(column).append(" ? ");
					paras.add("%" + value + "%");
				}else if("=".equals(queryType)) {
					whereSql.append(column).append(" =? ");
					paras.add(value);
				}else{
					whereSql.append(column).append(" like ? ");
					paras.add("%" + value + "%");
				}
			}
		}

		if(whereSql.length()>0){
			if(sql.toLowerCase().contains("where")){
				sql+=" and "+whereSql.toString();
			}else{
				sql+=" where "+whereSql.toString();
			}
		}

		if (orderByGroupBySql != null) {
			sql += " " + orderByGroupBySql;
		}
		return sql;
	}

	/**
	 * select * from getTable()
	 * @return
	 */
	private String getQuerySql() {			
		return "select * from "+getTable()+" ";
	}

	private String getSelectSql() {
		return "select *  ";
	}

	public String getExceptSql(String orderByGroupBy,Model<?> entity,List<Object> paras,String queryType)
	{
		return getExceptSql("",orderByGroupBy,entity,null,paras,queryType);
	}

	public String getExceptSql(String sql,String orderByGroupBy,Model<?> entity,List<Object> paras,String queryType)
	{
		return getExceptSql(sql,orderByGroupBy,entity,null,paras,queryType);
	}

	public String getExceptSql(String sql, String orderByGroupBySql, Model<?> entity, HashMap<String, String> map, List<Object> paras, String queryType)
	{
		if(StringUtils.isEmpty(sql))
		{
			sql=" from "+getTable()+"";
		}
		String[] columns=entity._getAttrNames();
		Object[] values=entity._getAttrValues();
		StringBuffer whereSql=new StringBuffer();
		for (String column:columns) {
			Object value=entity.get(column);
			if(value!=null&&value.toString().trim().length()>0){
				if(whereSql.length()>0){
					whereSql.append(" and ");
				}
				//用法看用户管理查询功能
				if(column.endsWith("=")){                              //column=、column<=、column>=
					whereSql.append(column).append(" ? ");
					paras.add(value);
				} else if(column.endsWith(">")||column.endsWith("<")){ //column<、column>
					whereSql.append(column).append(" ? ");
					paras.add(value);
				}else if(column.toLowerCase().endsWith("like")){       //column like
					whereSql.append(column).append(" ? ");
					paras.add("%" + value + "%");
				}else if("=".equals(queryType)) {
					whereSql.append(column).append(" =? ");
					paras.add(value);
				}else{
					whereSql.append(column).append(" like ? ");
					paras.add("%" + value + "%");
				}
			}
		}

		if(map!=null)
		{
			Set set = map.entrySet();
			Iterator it = set.iterator();
			if(it.hasNext()) {
				Map.Entry me = (Map.Entry) it.next();
				String column=me.getKey().toString();
				String value = me.getValue().toString();
				if(value!=null&&value.toString().trim().length()>0){
					if(whereSql.length()>0){
						whereSql.append(" and ");
					}
					//用法看用户管理查询功能
					if(column.endsWith("=")){                              //column=、column<=、column>=
						whereSql.append(column).append(" ? ");
						paras.add(value);
					} else if(column.endsWith(">")||column.endsWith("<")){ //column<、column>
						whereSql.append(column).append(" ? ");
						paras.add(value);
					}else if(column.toLowerCase().endsWith("like")){       //column like
						whereSql.append(column).append(" ? ");
						paras.add("%" + value + "%");
					}else if("=".equals(queryType)) {
						whereSql.append(column).append(" =? ");
						paras.add(value);
					}else{
						whereSql.append(column).append(" like ? ");
						paras.add("%" + value + "%");
					}
				}
			}
		}

		if(whereSql.length()>0){
			if(sql.toLowerCase().contains("where")){
				sql+=" and "+whereSql.toString();
			}else{
				sql+=" where "+whereSql.toString();
			}
		}
		
		if (orderByGroupBySql != null) {
			sql += " " + orderByGroupBySql;
		}
		return sql;
	}
}
